package com.diapoclic.entitydao;
import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
//import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Date;
import java.text.SimpleDateFormat;

import javax.servlet.ServletException;


import com.diapoclic.entitybean.*;


public class UserDao {

	    private Connect connectiondb = new Connect();
		private Connection con = connectiondb.connect();	
		private SimpleDateFormat sdf =new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		
		private User createUser (ResultSet res) throws SQLException
		{
			User newUser = new User();
			newUser.setIdUser(res.getInt(1));
			newUser.setNameUser(res.getString(2));
			newUser.setFirstnameUser(res.getString(3));
			newUser.setMail(res.getString(4));
			newUser.setPassword(res.getString(5));
			newUser.setLogin(res.getString(6));
			newUser.setDateRegistration(res.getDate(7));
			newUser.setDateLastConnect(res.getDate(8));
			return newUser;
			
		}
		
		public Connection getCon ()
		{
			return this.con;
		}
		
		public Connect getConnexiondb()
		{
			return this.connectiondb;
		}
		
		public String helloWorld() throws SQLException
		{//dans cette méthode on fait un appel à la base de donnée pour vérifier qu'elle est bien interrogée
			
			//Création d'un objet Statement
			//Statement state = con.createStatement();
			//L'objet ResultSet contient le résultat de la requête SQL
		//	ResultSet result = state.executeQuery("SELECT * FROM User");
			//On récupère les MetaData
			//ResultSetMetaData resultMeta = result.getMetaData();
			/*
			System.out.println("\n**********************************");
			//On affiche le nom des colonnes
			for(int i = 1; i <=  resultMeta.getColumnCount(); i++)
				System.out.print("\t" + resultMeta.getColumnName(i).toUpperCase() + "\t *");
			System.out.println("\n**********************************");
		 	*/
			return "Hello World, la connexion avec la base de donnée est établie et fonctionne!";
		}
		
		public User findById(int id) throws SQLException
		{
			User newUser = new User();
			String query;
			Statement state = con.createStatement();
			query="SELECT * FROM User WHERE id_user="+id;
			ResultSet result = state.executeQuery(query);
			
			if (result.next()) {
				 newUser=createUser(result);
				 
			 }
			
			return newUser;
		}
		
		public List<User> findByIds(int[] ids) throws SQLException
		{
			List<User> tabUser=new ArrayList<User>();
			String query;

			
			Statement state = con.createStatement();
			for(int i=0;i<ids.length;i++)
			{
			query="SELECT * FROM User WHERE id_user="+ids[i];
			ResultSet result = state.executeQuery(query);
			if (result.next()) {
				 tabUser.add(createUser(result));
			 }
			
			}
			return tabUser ;
		}
		
		public User findByFirstname(String firstname) throws SQLException
		{
			User newUser = new User();
			String query;
			Statement state = con.createStatement();
			query="SELECT * FROM User WHERE firstname_user='"+firstname+"'";
			
			ResultSet result = state.executeQuery(query);
			
			if (result.next()) {
				 newUser=createUser(result);
			 }
			
			return newUser;
		}
		
		public User findByName(String name) throws SQLException
		{
			User newUser = new User();
			String query;
			Statement state = con.createStatement();
			query="SELECT * FROM User WHERE name_user='"+name+"'";
			
			ResultSet result = state.executeQuery(query);
			
			if (result.next()) {
				 newUser=createUser(result);
			 }
			
			return newUser;
		}
		
		public User findByPassword(String password) throws SQLException
		{
			User newUser = new User();
			String query;
			Statement state = con.createStatement();
			query="SELECT * FROM User WHERE password='"+password+"'";
			
			ResultSet result = state.executeQuery(query);
			
			if (result.next()) {
				 newUser=createUser(result);
			 }
			
			return newUser;
		}
		public User findByMail(String mail) throws SQLException
		{
			User newUser = new User();
			String query;
			Statement state = con.createStatement();
			query="SELECT * FROM User WHERE mail='"+mail+"'";
			
			ResultSet result = state.executeQuery(query);
			
			if (result.next()) {
				 newUser=createUser(result);
			 }
			
			return newUser;
		}
		public User findByLogin(String login) throws SQLException
		{
			User newUser = new User();
			String query;
			Statement state = con.createStatement();
			query="SELECT * FROM User WHERE login='"+login+"'";
			
			ResultSet result = state.executeQuery(query);
			
			if (result.next()) {
				 newUser=createUser(result);
			 }

			return newUser;
		}
		
		public int addUser (String newName, String newFirstname, String newMail, String newPassword, String newLogin) throws SQLException 
		{
			Date today=new Date();
			String query;
			
			//Enregistrement dans la base de données
			Statement state = con.createStatement();
			query="INSERT INTO `diapoclic`.`User` (`id_user`, `name_user`, `firstname_user`, `mail`, `password`, `login`, `date_registration`, `date_last_connect`) "; 
			query+="VALUES (NULL, '" +newName+ "', '" +newFirstname+ "', '" +newMail+ "', '" +newPassword+ "', '" +newLogin+ "', '" +sdf.format(today)+ "', '" +sdf.format(today)+ "');";
			int result = state.executeUpdate(query);

			//Création d'un dossier de stockage pour les photos

			String pathname = System.getProperty("user.dir")+"/webapps/diapoclic/photos/";
			String directory = pathname + newLogin;
			new File(directory).mkdirs();
			System.out.println(pathname);
			

			return result;
		}
		
		public int updateUser(User userUpdate) throws SQLException
		{
			Date today=new Date();
			String query;
			Statement state = con.createStatement();
			query="UPDATE `diapoclic`.`User` SET";
			query+="`name_user` = '" +userUpdate.getNameUser()+ "',";
			query+="`firstname_user` = '" +userUpdate.getFirstnameUser()+ "',";
			query+="`mail` = '" +userUpdate.getMail()+ "',";
			query+="`password` = '" +userUpdate.getPassword()+ "',";
			query+="`date_last_connect` = '" +sdf.format(today)+ "'";		
			query+="WHERE `User`.`id_user` ="+userUpdate.getIdUser()+";";
			
			int result = state.executeUpdate(query);

			return result;
			
		}
		
		public List<User> listFriend(int idUser) throws SQLException
		{
			List<User> tabFriend=new ArrayList<User>();
			String query;
			Statement state = con.createStatement();
			query="SELECT id_friend FROM Friend WHERE id_user="+idUser;
			ResultSet result = state.executeQuery(query);
			
			
			while (result.next()) {
				tabFriend.add(findById(result.getInt(1)));
			 }

			return tabFriend ;
			
		}
		
		
		public int addFriend (int idUser,int idFriend) throws SQLException
		{
			String query;
			Statement state = con.createStatement();
			query="INSERT INTO `diapoclic`.`Friend` (`id_friend`, `id_user`) "; 
			query+="VALUES ('" +idFriend+ "','" +idUser+ "');";
						
			int result = state.executeUpdate(query);
			
			

			return result;
		}
	
		
		public int removeFriend (int idUser,int idFriend) throws SQLException
		{
			String query;
			Statement state = con.createStatement();
			query="DELETE FROM `diapoclic`.`Friend` WHERE `Friend`.`id_friend` =" +idFriend+ " && `id_user`=" +idUser+ ";"; 
			
			
			int result = state.executeUpdate(query);

			return result;
		}
		
		
		public int addFolderShared (int idFolder,int idUser,int idFriend) throws SQLException
		{
			String query;
			Statement state = con.createStatement();
			query="INSERT INTO `diapoclic`.`FolderShared` (`id_folderSh`, `id_user`,`id_friend`) "; 
			query+="VALUES ('" +idFolder+ "','" +idUser+ "','" +idFriend+ "');";
			
			
			int result = state.executeUpdate(query);

			return result;
		}
	
		
		public int removeFolderShared (int idFolder,int idUser,int idFriend) throws SQLException
		{
			String query;
			Statement state = con.createStatement();
			query="DELETE FROM `diapoclic`.`FolderShared` WHERE `Friend`.`id_friend` =" +idFriend+ " && `id_folderSh`=" +idFolder+ "&& `id_user`=" +idUser+ ";"; 
					
			int result = state.executeUpdate(query);

			return result;
		}
			
}
